﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Web.Mvc;
using System.Data.SqlClient;

namespace BookingManagementSystem.Models
{
    public static class DataAccessLayer
    {
        static OleDbConnection GetConnection()
        {
            var dataDir = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
            return new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\db.mdb", dataDir));
        }

        public static List<SelectListItem> GetAllCustomers()
        {
            var customers = new List<SelectListItem>();
            using (IDbConnection connection = GetConnection())
            {
                string commandString = "select customer_id, customer_name from t_customer";
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            customers.Add(new SelectListItem { Text = reader.GetString(1), Value = reader.GetInt32(0).ToString()});
                        }
                    }
                }
            }
            return customers;
        }

        public static List<SelectListItem> GetAllOrigins()
        {
            var origins = new List<SelectListItem>();
            using (IDbConnection connection = GetConnection())
            {
                string commandString = "select origin_id, origin_desc from t_origin";
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            origins.Add(new SelectListItem { Text = reader.GetString(1), Value = reader.GetInt32(0).ToString() });
                        }
                    }
                }
            }
            return origins;
        }

        public static List<SelectListItem> GetAllRooms()
        {
            var rooms = new List<SelectListItem>();
            using (IDbConnection connection = GetConnection())
            {
                string commandString = "select room_id, room_desc from t_room";
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; 
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            rooms.Add(new SelectListItem { Text = reader.GetString(1), Value = reader.GetInt32(0).ToString() });
                        }
                    }
                }
            }
            return rooms;
        }

        public static void AddBooking(BookingModel booking)
        {
            using (IDbConnection connection = GetConnection())
            {
                connection.Open();
                string command = @"INSERT INTO t_booking
                                (room_id,customer_id,origin_id,booking_checkin,booking_checkout)
                                values
                                (@roomId,@customerId,@originId,@checkIn,@checkOut)";
                using (OleDbCommand com = new OleDbCommand(command, (OleDbConnection)connection))
                {
                    com.Parameters.AddWithValue("@roomId", System.Data.OleDb.OleDbType.Integer).Value = booking.RoomId;
                    com.Parameters.AddWithValue("@customerId", System.Data.OleDb.OleDbType.Integer).Value = booking.CustomerId;
                    com.Parameters.AddWithValue("@originId", System.Data.OleDb.OleDbType.Integer).Value = booking.OriginId;
                    com.Parameters.AddWithValue("@checkIn", System.Data.OleDb.OleDbType.DBDate).Value = booking.CheckInDate;
                    com.Parameters.AddWithValue("@checkOut", System.Data.OleDb.OleDbType.DBDate).Value = booking.CheckOutDate;
                    com.ExecuteNonQuery();
                }
            }
        }

        public static void DeleteBooking(int idBooking)
        {
            using (IDbConnection con = GetConnection())
            {
                con.Open();
                string command = @"DELETE FROM t_booking
                                    WHERE booking_id = @idBooking";
                using (OleDbCommand com = new OleDbCommand(command, (OleDbConnection)con))
                {
                    com.Parameters.AddWithValue("@idBooking", System.Data.OleDb.OleDbType.Integer).Value = idBooking;
                    com.ExecuteNonQuery();
                }
            }
        }

        public static List<BookingModel> GetAllBookings()
        {
            var bookings = new List<BookingModel>();
            using (IDbConnection connection = GetConnection())
            {
                string commandString = "select booking_id,room_id,customer_id,origin_id,booking_checkin,booking_checkout from t_booking";
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bookings.Add(new BookingModel { 
                                Id = reader.GetInt32(0), 
                                CustomerId = reader.GetInt32(1), 
                                OriginId = reader.GetInt32(2), 
                                RoomId = reader.GetInt32(3), 
                                CheckInDate = reader.GetDateTime(4),
                                CheckOutDate = reader.GetDateTime(5)
                            });
                        }
                    }
                }
            }
            return bookings;
        }

        public static BookingModel GetBookingById(int idBooking)
        {
            var bookings = new List<BookingModel>();
            using (OleDbConnection connection = (OleDbConnection)GetConnection())
            {
                string commandString = "select booking_id,room_id,customer_id,origin_id,booking_checkin,booking_checkout from t_booking where booking_id = @idBooking";
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; 
                    connection.Open();
                    command.Parameters.AddWithValue("@idBooking", System.Data.OleDb.OleDbType.Integer).Value = idBooking;
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bookings.Add(new BookingModel
                            {
                                Id = reader.GetInt32(0),
                                CustomerId = reader.GetInt32(1),
                                OriginId = reader.GetInt32(2),
                                RoomId = reader.GetInt32(3),
                                CheckInDate = reader.GetDateTime(4),
                                CheckOutDate = reader.GetDateTime(5)
                            });
                        }
                    }
                }
            }
            return bookings[0];
        }

        public static void EditBooking(BookingModel booking)
        {
            using (IDbConnection connection = GetConnection())
            {
                connection.Open();
                string command = @"UPDATE t_booking
                                 SET room_id = @idRoom,
                                customer_id = @idCustomer,
                                origin_id = @idOrigin,
                                booking_checkin = @checkIn,
                                booking_checkout = @checkOut
                                WHERE booking_id = @idBooking";
                using (OleDbCommand com = new OleDbCommand(command, (OleDbConnection)connection))
                {
                    com.Parameters.AddWithValue("@idRoom", System.Data.OleDb.OleDbType.Integer).Value = booking.RoomId;
                    com.Parameters.AddWithValue("@idCustomer", System.Data.OleDb.OleDbType.Integer).Value = booking.CustomerId;
                    com.Parameters.AddWithValue("@idOrigin", System.Data.OleDb.OleDbType.Integer).Value = booking.OriginId;
                    com.Parameters.AddWithValue("@checkIn", System.Data.OleDb.OleDbType.Date).Value = booking.CheckInDate;
                    com.Parameters.AddWithValue("@checkOut", System.Data.OleDb.OleDbType.Date).Value = booking.CheckOutDate;
                    com.Parameters.AddWithValue("@idBooking", System.Data.OleDb.OleDbType.Integer).Value = booking.Id;
                    com.ExecuteNonQuery();
                }
            }
        }
    }
}